How to optimise Texpress

To ensure that Texpress continues to run efficiently, all Vitalware tables should be optimised regularly. The frequency at which optimisation is performed will depend on the size of the table and the volume of new records and record updates.

There are six steps to the optimisation procedure.

  1. Open a command line session on the Vitalware server and log in with the username vw.
  2. Type client [client] to go to the required Vitalware client environment.
  3. Type texlist -lbr

    This will display a list of tables, the number of records in each and how much of the space allocated to each table has been used.

    The list is sorted from longest disk usage to smallest disk usage:

    Texlist

    If the amount of space used is close to or over 100%, the space allocated to the table needs to be increased.

  4. Make a note of the number of records in the table that is to be optimised.

    Note: Values that are around 90% are acceptable. To sort the values by order of percentage, type texlist -lspr

  1. Type texadmin [name of table]

    For example, type texadmin eparties

  2. Type C to view the configuration settings.
  3. Make a note of the size allocated to the table.

    If it is obvious that the size needs to be increased based on the number of current records, increase the table size. Texpress will automatically adjust the rest of the settings in accordance with this figure.

    If you change the size of the table here, you do not need to continue with the remaining optimisation steps. However, you must re-index the table before these configuration settings take affect.

    Note: Settings configured by Texpress may not be the optimal settings. To calculate the optimal settings, continue with Step 3.

Note: Details about texanalyse can be found under Configuration Tools.

Before you run texanalyse, create a directory in which to store the texanalyse report.

  1. Type cd [new directory]
  2. Type texanalyse [name of table] > [name of table].conf

    For example, texanalyse eparties > eparties.conf

  3. To view the texanalyse file, type vi [name of table].conf

    For example, vi eparties.conf

    This will display an analysis of the number of indexed words (atoms) in each record:

       |   Atoms   |    Records    |
       +-----------+---------------+
       |    41     |       2       |
       |    42     |       1       |
       |    43     |      29       |
       |    44     |       3       |
       |    45     |     177       |
       |    46     |       9       |
       |    47     |      27       |
       |    48     |       5       |
       |    49     |       1       |
  4. Move to the end of the file: 
       Total number of records                   :          311
       Total number of indexed terms             :        14801
       Average number of indexed terms per record:           47.6
       Records <= average                     248 (79.7% <= 47.6)
       Records <= average + 1 * sqrt(average) 265 (85.2% <= 54.5)
       Records <= average + 2 * sqrt(average) 296 (95.2% <= 61.4)
       Records <= average + 3 * sqrt(average) 301 (96.8% <= 68.3)
       Records <= average + 4 * sqrt(average) 308 (99.0% <= 75.2)
       Records <= average + 5 * sqrt(average) 311 (100.0% <= 82.1)
  5. Note the number of indexed terms that are equal to 100%. This is the i value that will be used when you run the texconf file in Step 4.

    Note: Using the i value that is equal to 100% may not be appropriate for large systems. There must be a trade off between the number of records (100%) and keeping the number of index terms down. The number of index terms dramatically affects the index, so in most cases a value around 98.5 - 99.5% works well.

  6. Exit from vi.

Note: Details about texaconf can be found under Configuration Tools.

The tag length is the number of bits that are tagged on to a record level. Tag bits are used for range searching.

To find the tag length for the table:

  1. Type texconf -d[name of table] 1

    For example, texconf eparties 1

    This will display something similar to the following:

   Capacity of database (in records)       :        24
   Number of segments (Ns)                 :         8
   Number of records per segment (Nr)      :         3
   Words per segment descriptor            :       103
   Words per record descriptor             :        82
   Bits set per indexed term (segment)     :         2
   Bits set per indexed term (record)      :         5
   False match probability (segment)       :   .031250
   [1 / (4.0 * Ns)]
   False match probability (record)        :  0.000326
   [1 / (1024.0 * Nr)]
   Expected number of indexed terms        :        71
   Record descriptor tag length (bits)     :       112
   Segment size                            :      1024
   Segment descriptor file size            :     3.22K
   Record descriptor file size             :     8.00K
   Percent of record descriptor file wasted:      2.73%
   Total index overhead                    :     11.22K

Note the Record descriptor tag length (bits). This figure is used when running the texconf program in Step 5.

The texconf program calculates the settings for the optimal running of the table.

Note: Details about texaconf can be found under Configuration Tools.

To run the texconf program:

  1. Type texconf -i[i] -t[tag number] capacity

    For example, texconf -i82 -t112 capacity

    where i is the number of index terms determined from texanalyse and -t is the Record descriptor tag length (bits) determined at Step 4. The capacity is the number of records that the table will contain.

    Note: The capacity may be exceeded but this is at the expense of performance.

 vw-gallery@kestrel[22] texconf -i82 -t112 350 capacity
   Capacity of database (in records)           :       352
   Number of segments (Ns)                     :       176
   Number of records per segment (Nr)          :         2
   Words per segment descriptor                :       148
   Words per record descriptor                 :       126
   Bits set per indexed term (segment)         :         4
   Bits set per indexed term (record)          :         5

2. Record these values.

  1. The settings specified below will not take affect until the table index has been rebuilt. A rebuild may take considerable time and will take the table off-line for the duration. It is preferable to delay the index rebuild until it can be run as a maintenance task after hours.
  2. After the new configuration is saved, users will not be able to access the table until the table index is rebuilt.

To update the table configuration with the optimal configuration calculated by the texconf program:

  1. Type texadmin [name of table]

    For example, texadmin eparties

  2. Type c to view the table settings.
  3. Overwrite the settings with those calculated by the texconf program at Step 5.

    Use F5 to move forward a field, F6 to move back a field and ENTER to complete a number.

    Configuration

  4. Press F4 to save the settings.

    A prompt displays asking if the changes require a table rebuild.

  5. Select Yes.

    A prompt displays asking if you want to configure the table index ready for rebuild.

  6. Select Yes.
  7. Press any key to complete the configuration changes.
  8. Press F1 to exit texadmin.

With this optional step it is possible to set configuration variables on a per table basis so that settings are not lost with the next reconfiguration.

Tip: To fully understand this step, you may need to review Texpress: optimising and configuring the database engine, which describes in detail how to achieve optimal indexing for Texpress tables.

As described in Texpress: optimising and configuring the database engine (and using the configuration tools: texanalyse, texdensity and texconf) it is possible to achieve optimal indexes in both speed and size, but it is not much help if the next reconfiguration of the table loses all the settings. Fortunately Texpress provides a database file in which to store the configuration settings. Settings found in this file override the default values used by Texpress.

The file in which configuration parameters can be stored is called params. It is an optional file. The file contents are XML based and contain the configuration variables to be overridden. A complete listing of the file is (with the default values set):

<params>
        <configuration>
                <blocksize>4096</blocksize>
                <record>
                        <atoms></atoms>
                        <density>25</density>
                        <falsematch>1024</falsematch>
                        <variance>3.0</variance>
                        <scan></scan>
                </record>
                <segment>
                        <atoms></atoms>
                        <density>25</density>
                        <falsematch>4</falsematch>
                        <variance>2.0</variance>
                        <scan></scan>
                        <minimum>10</minimum>
                </segment>
        </configuration>
</params>

The table below explains the use of each tag:

Tag

Description

blocksize

The underlying block size used by the filesystem on with the Texpress table is stored.

atoms

The number of atoms per record (i).

density

Bit density to use. Value between 1 and 99 (d).

falsematch

False match probability (f).

variance

Number of standard deviations to add to average (v).

scan

Number of records to use to calculate atoms.

minimum

Minimum number of records per segment.

To alter the number of atoms per record at the segment level to use 33, the following params file could be used:

<params>
        <configuration>
                <segment>
                        <atoms>33</atoms>
                </segment>
        </configuration>
</params>

Values set in the params file are also used by texconf, so running texconf will use 33 for the number of atoms per record for the segment descriptor. You can use the texconf options to override the value in the params file (texconf -is40 einvoices will use a value of 40 for the number of atoms when calculating the length of the segment descriptor).

In most cases the values generated by the new configuration facility will provide near optimal indexes. In some rare cases it may be necessary to "tune" the configuration parameters to achieve savings in disk space (particularly at the segment level). If tuning is required, it is better to alter the variance value rather than specifying the number of atoms to use. The reason is as the database grows, the average number of atoms per record will vary. If a variance is specified, the value for atoms can vary with it. So if we take the case of the einvoices table which had an average number of atoms of 64.7 and a standard deviation of 9.1, we can set the variance to -3.5 (yes you can use negative variances) to get the number of indexed terms to be 32. In this case the following params file could be used:

<params>
        <configuration>
                <segment>
                        <variance>-3.5</variance>
                </segment>
        </configuration>
</params>